package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mysql.jdbc.EscapeTokenizer;

import entity.Category;
import entity.Query;
import entity.Record;
import gui.panel.LoginPanel;
import gui.panel.QueryPanel;
import util.DBUtil;
import util.DateUtil;

public class QueryDAO {
	static LoginPanel lp = LoginPanel.instance;
	public int getTotal() {
		int total = 0;
		try (Connection c = DBUtil.getConnection();Statement s = c.createStatement();){
			String sql = "select count(*) from record where uaccount = " + lp.tfAccount.getText();
			
			ResultSet rSet = s.executeQuery(sql);
			
			while(rSet.next()) {
				total = rSet.getInt(1);
			}
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return total;
	}
	
	public void update(Query query) {
		 
        String sql = "update record set spend= ?, cid=? , comment=?, date=? where id = ?";
        try (Connection c = DBUtil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {
 
            ps.setInt(1, query.spend);
            ps.setInt(2, query.cid);
            ps.setString(3, query.comment);
            ps.setDate(4, query.date);
            ps.setInt(5, query.id);
 
            ps.execute();
 
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
 
    }
	
	public void delete(int id) {
		 
        try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
 
            String sql = "delete from record where id = " + id;
 
            s.execute(sql);
 
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
    }
	
	public Query get(int id) {
        Query query = null;
 
        try (Connection c = DBUtil.getConnection(); Statement s = c.createStatement();) {
 
            String sql = "select * from record where id = " + id;
 
            ResultSet rs = s.executeQuery(sql);
 
            if (rs.next()) {
                query = new Query();
                int spend = rs.getInt("spend");
                int cid = rs.getInt("value");
                String comment = rs.getString("comment");
                Date date = rs.getDate("date");
                query.spend = spend;
                query.cid = cid;
                query.comment = comment;
                query.date = DateUtil.util2sql(query.date);
            }
 
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
        return query;
    }
	
	public List<Query> list() {
        return list(0, Short.MAX_VALUE);
    }
	
	public List<Query> list(int start, int count) {
        List<Query> querys = new ArrayList<Query>();
 
        String sql = "select * from record where uaccount = ? order by date asc limit ?,? ";
        String sqlcid = "select name from category where id = ?";
 
        try (Connection c = DBUtil.getConnection(); 
        		PreparedStatement ps = c.prepareStatement(sql);
        		PreparedStatement pscid=c.prepareStatement(sqlcid);) {
        	
        	ps.setString(1, lp.tfAccount.getText());
            ps.setInt(2, start);
            ps.setInt(3, count);
 
            ResultSet rs = ps.executeQuery();
 
            while (rs.next()) {
            	Query query = new Query();
                int id = rs.getInt(1);
                int spend = rs.getInt(2);
                int cid = rs.getInt(3);
                pscid.setString(1, String.valueOf(cid));
                ResultSet rscid = pscid.executeQuery();
                String cname = null;
                
                if(rscid.next()) {
                	cname = rscid.getString("name");
                }
                 
                 String comment = rs.getString(4);
                 java.sql.Date date = rs.getDate(5);
                  
                 query.spend=spend;
                 query.cname=cname;
                 query.cid=cid;
                 query.comment=comment;
                 query.date=date;
                 query.id = id;
                querys.add(query);
            }
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
        return querys;
    }
	
	public List<Query> listReQueries(String tfSpendl,String tfSpendr,Date datepickerl,Date datepickerr,String tfComment) {
        List<Query> querys = new ArrayList<Query>();
        int i = 1;
        String sql = "select * from record where uaccount = ? ";
        
        if(tfSpendl.equals("") == false) {
        	sql = sql + "and spend >= ? ";
        }
        if(tfSpendr.equals("") == false) {
        	sql = sql + "and spend <= ? ";
        }
        if(datepickerl != null) {
        	sql = sql + "and date >= ? ";
        }
        if(datepickerr != null) {
        	sql = sql + "and date <= ? ";
        }
        if(tfComment.equals("") == false) {
        	sql = sql + "and comment = ? ";
        }
//        System.out.println(sql);
        String sqlcid = "select name from category where id = ?";
 
        try (Connection c = DBUtil.getConnection(); 
        		PreparedStatement ps = c.prepareStatement(sql);
        		PreparedStatement pscid=c.prepareStatement(sqlcid);) {
        	
        	ps.setString(1, lp.tfAccount.getText());
        	if(tfSpendl.equals("") == false) {
        		i++;
            	ps.setInt(i, Integer.parseInt(tfSpendl));
            }
        	if(tfSpendr.equals("") == false) {
        		i++;
            	ps.setInt(i, Integer.parseInt(tfSpendr));
            }
        	if(datepickerl != null) {
        		i++;
            	ps.setDate(i,new java.sql.Date (datepickerl.getTime()));
            }
        	if(datepickerr != null) {
        		i++;
            	ps.setDate(i, new java.sql.Date (datepickerr.getTime()));
            }
        	if(tfComment.equals("") == false) {
        		i++;
        		ps.setString(i, tfComment);
            }
        	
 
            ResultSet rs = ps.executeQuery();
 
            while (rs.next()) {
            	Query query = new Query();
                int id = rs.getInt(1);
                int spend = rs.getInt(2);
                int cid = rs.getInt(3);
                pscid.setString(1, String.valueOf(cid));
                ResultSet rscid = pscid.executeQuery();
                String cname = null;
                
                if(rscid.next()) {
                	cname = rscid.getString("name");
                }
                 
                 String comment = rs.getString(4);
                 java.sql.Date date = rs.getDate(5);
                  
                 query.spend=spend;
                 query.cname=cname;
                 query.cid=cid;
                 query.comment=comment;
                 query.date=date;
                 query.id = id;
                querys.add(query);
            }
        } catch (SQLException e) {
 
            e.printStackTrace();
        }
        return querys;
    }
}
